package org.greenstone.gsdl3.service;

import java.util.* ;
import java.text.* ;
import java.sql.Statement ;
import java.sql.ResultSet ;
import java.sql.SQLException ;
import org.w3c.dom.*;
import org.greenstone.gsdl3.util.*;

public class KoruGameDatabase extends MySqlDatabase {

	KoruGameServices parent ;

	public KoruGameDatabase(String server, String databaseName, String userName, String password, KoruGameServices parent) throws Exception{

		super(server, databaseName, userName, password) ;
		this.parent = parent ;

		try {
			checkDatabase() ;			
		} catch (SQLException e) {
			System.out.println("WARNING: database is incomplete.") ;
			e.printStackTrace() ;
		}
	}

	public Statement createStatement() throws SQLException{

		if (this.connection.isClosed() || this.connection == null)
			connect() ;

		return super.createStatement() ;
	}

	public void checkDatabase() throws SQLException {

	}

	public int getTotalTasks() throws SQLException {
		int tasks = 0 ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM tasks") ;

		if (rs.first()) {
			tasks = rs.getInt(1) ; 
		}

		rs.close() ;
		stmt.close() ;

		return tasks ;
	}

	public int getTotalPlayers() throws SQLException {
		int players = 0 ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM players") ;

		if (rs.first()) {
			players = rs.getInt(1) ; 
		}

		rs.close() ;
		stmt.close() ;

		return players ;
	}

	public Vector<KoruTask> getAllTasks() throws SQLException {
		Vector<KoruTask> tasks = new Vector<KoruTask>() ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT id FROM tasks") ;

		while (rs.next()) {
			int id  = rs.getInt(1) ;

			KoruTask task = new KoruTask(id) ;
			tasks.add(task) ;
		}

		rs.close() ;
		stmt.close() ;



		return tasks ;
	}

	public Vector<KoruPlayer> getTopPlayers() throws SQLException {

		Vector<KoruPlayer> players = new Vector<KoruPlayer>() ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT player, COUNT(task) 'tasks', AVG(top_score) 'average', SUM(top_score) 'total' FROM taskScores GROUP BY player ORDER BY total DESC LIMIT 10 ;") ;

		while (rs.next())  {
			KoruPlayer player = new KoruPlayer(rs.getString(1)) ;
			players.add(player) ;
		}

		rs.close() ;
		stmt.close() ;

		return players ;
	}

	public Vector<KoruPlayer> getNeighbors(KoruPlayer player) throws SQLException {

		Vector<KoruPlayer> neighbors = new Vector<KoruPlayer>() ;

		int rank = player.getRanking() ;

		if (rank < 10)
			return null;

		int startIndex = rank - 5 ;
		if (startIndex < 10)
			startIndex = 10 ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT player, COUNT(task) 'tasks', AVG(top_score) 'average', SUM(top_score) 'total' FROM taskScores GROUP BY player ORDER BY total DESC LIMIT " + startIndex + ",10 ;") ;

		while (rs.next()) {
			KoruPlayer p = new KoruPlayer(rs.getString(1)) ;
			neighbors.add(p) ;
		}

		rs.close() ;
		stmt.close() ;

		return neighbors ;		

	}

	public KoruPlayer getPlayer(String name) throws SQLException{

		KoruPlayer player = null ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT * FROM players WHERE name=\"" + name + "\"") ;

		if (rs.first()) {
			player = new KoruPlayer(name) ; 
		}

		rs.close() ;
		stmt.close() ;

		return player ;
	}

	public KoruTask getTask(int taskId) throws SQLException {
		return new KoruTask(taskId) ;
	}

	public void postQuery(String player, int taskId, String query, double recall, double precision, double recall_fp, double precision_fp, int score, int docs_returned, int rel_docs_returned, int rel_docs_total) throws SQLException {

		Statement stmt = createStatement() ;
		stmt.executeUpdate("INSERT IGNORE INTO queries VALUES (\"" + player + "\"," + taskId + ", CURRENT_TIMESTAMP, \"" + this.addEscapes(query) + "\", " + recall + "," + precision + "," + recall_fp + "," + precision_fp + "," + score + "," + docs_returned + "," + rel_docs_returned + "," + rel_docs_total + ")") ;
		stmt.close() ;		
	}

	public void postTaskScore(String player, int taskId, int score) throws SQLException{

		Statement stmt = createStatement() ;
		stmt.executeUpdate("INSERT INTO completedTasks VALUES (\"" + player + "\"," + taskId + "," + score + ")") ;
		stmt.close() ;
	}

	public int getRelevance(int taskId, String docId) throws SQLException {

		int relevance = 0 ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT relevance FROM relevanceJudgements WHERE task=" + taskId + " AND document=\"" + docId + "\"") ;

		if (rs.first()) {
			relevance = rs.getInt(1) ;
		}

		rs.close() ;
		stmt.close() ;

		return relevance ;
	}

	public int getRelevantDocsForTask(int taskId) throws SQLException {

		int docs = 0 ;


		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM relevanceJudgements WHERE task=" + taskId + " AND relevance=2") ;

		if (rs.first()) {
			docs = rs.getInt(1) ;
		}

		rs.close() ;
		stmt.close() ;

		return docs ;
	}

	public boolean nameRegistered(String name) throws SQLException{
		boolean registered = false ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT * FROM players WHERE name=\"" + name + "\"") ;

		if (rs.first()) {
			registered = true ;
		}

		rs.close() ;
		stmt.close() ;

		return registered ;
	}

	public boolean emailRegistered(String email) throws SQLException{
		boolean registered = false ;

		Statement stmt = createStatement() ;
		ResultSet rs = stmt.executeQuery("SELECT * FROM players WHERE email=\"" + email + "\"") ;

		if (rs.first()) {
			registered = true ;
		}

		rs.close() ;
		stmt.close() ;

		return registered ;
	}

	public void registerPlayer(String name, String email, String password) throws SQLException{

		Statement stmt = createStatement() ;
		stmt.executeUpdate("INSERT INTO players VALUES(\"" + email + "\",\"" + name + "\",\"" + password + "\")") ;
		stmt.close() ;
	}

	public class KoruTask {
		int id ;
		String title ;
		String desc ;
		String narr ;

		public KoruTask(int id) throws SQLException{
			this.id = id ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT * FROM tasks WHERE id=\"" + id + "\"") ;

			if (rs.first()) {
				title = rs.getString(2) ;
				desc = rs.getString(3) ;
				narr = rs.getString(4) ;
			}

			rs.close() ;
			stmt.close() ;
		}

		public TreeSet<String> getRelevantDocuments() throws SQLException{

			TreeSet<String> docs = new TreeSet<String>() ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT document FROM relevanceJudgements WHERE task=\"" + id + "\" AND relevance=2") ;

			while (rs.next()) {
				docs.add(rs.getString(1)) ;
			}

			rs.close() ;
			stmt.close() ;			

			return docs ;
		}


		public int getTopScore() throws SQLException {

			int score = 0 ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT MAX(score) FROM completedTasks WHERE task=" + id ) ;

			while (rs.next()) {
				score = rs.getInt(1) ;
			}

			rs.close() ;
			stmt.close() ;			

			return score ;
		}

		public String getInitialQuery() {

			String query = "" ;

			for (int i=0 ; i<title.length() ; i++) {

				char ch = title.charAt(i) ;

				if (Character.isLetterOrDigit(ch)) {
					query = query + Character.toLowerCase(ch) ;
				}

				if (ch == '-')
					query = query + "-" ;

				if (Character.isWhitespace(ch)) {
					query = query + " " ;
				}
			}

			return query ;
		}

		public Element getXml() throws SQLException {

			Element task = parent.doc.createElement("task") ;
			task.setAttribute("id", String.valueOf(id)) ;
			task.setAttribute("title", title) ;
			task.setAttribute("description", desc) ;
			task.setAttribute("narrative", narr) ;
			task.setAttribute("relevantDocs", String.valueOf(getRelevantDocuments().size())) ;
			task.setAttribute("topScore", String.valueOf(getTopScore())) ;
			task.setAttribute("initialQuery", getInitialQuery()) ;

			return task ;
		}

		public int getRanking(String playerName) throws SQLException{
			int rank = 0 ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT player, MAX(score) FROM queries WHERE task=" + this.id + " GROUP BY player ORDER BY MAX(score) DESC") ;

			int count = 0 ;

			while (rs.next()) {
				count ++ ;
				String player = rs.getString(1) ;

				//System.out.println(player + " vs " + playerName) ;

				if (player.equals(playerName)) {
					rank = count ;
					break ;
				}
			}

			rs.close() ;
			stmt.close() ;

			return rank ;
		}
	}

	public class KoruPlayer {

		String name ;

		public KoruPlayer(String name) {
			this.name = name ;
		}

		public int getTasksCompleted() throws SQLException{
			int tasks = 0 ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT COUNT(DISTINCT task) FROM queries WHERE player=\"" + name + "\"") ;

			if (rs.first()) {
				tasks = rs.getInt(1) ;
			}

			rs.close() ;
			stmt.close() ;

			return tasks ;
		}

		public int getAverageTaskScore() throws SQLException{
			int score = 0 ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT AVG(top_score) FROM taskScores WHERE player=\"" + name + "\"") ;

			if (rs.first()) {
				score = rs.getInt(1) ;
			}

			rs.close() ;
			stmt.close() ;

			return score ;
		}

		public int getTotalScore() throws SQLException {
			int score = 0 ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT SUM(top_score) FROM taskScores WHERE player=\"" + name + "\"") ;

			if (rs.first()) {
				score = rs.getInt(1) ;
			}

			rs.close() ;
			stmt.close() ;

			return score ;
		}

		public int getRanking() throws SQLException{

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT player, SUM(top_score) 'total', AVG(top_score) 'average' FROM taskScores Group By player ORDER BY total DESC, average DESC") ;

			int rank = 0 ;

			while (rs.next()) {
				rank ++ ;
				String player = rs.getString(1) ;

				if (player.equals(this.name)) {
					break ;
				}
			}

			rs.close() ;
			stmt.close() ;

			return rank ;
		}

		public String getPassword() throws SQLException {
			String password = "" ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT password FROM players WHERE name=\"" + name + "\"") ;

			if (rs.first()) {
				password = rs.getString(1) ;
			}

			rs.close() ;
			stmt.close() ;

			return password ;
		}

		private class RankRecord {
			int rank ;
			Date date ;

			public RankRecord(int rank, Date date) {
				this.rank = rank ;
				this.date = date ;
			}


			public String getTimeAgo() {

				long millis = System.currentTimeMillis() - date.getTime() ;

				long sec = 1000 ;
				long min = sec * 60 ;
				long hour = min * 60 ;
				long day = hour * 24 ;
				long week = day * 7 ;

				if (millis > week)
					return Math.round((double)millis/week) + " week(s) ago." ;

				if (millis > day)
					return Math.round((double)millis/day) + " day(s) ago." ;

				if (millis > hour)
					return Math.round((double)millis/hour) + " hour(s) ago." ;

				return Math.round((double)millis/min) + " minutes ago." ;
			}
		}


		public RankRecord getLastRank() throws SQLException, ParseException {

			Date now = new Date() ;

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;

			RankRecord r = null ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT time, rank FROM rankRecords WHERE player=\"" + name + "\" ORDER BY time DESC") ;

			Date oldDate = null ;
			int oldRank = -1 ;

			while (rs.next()) {
				Date date = df.parse(rs.getString(1)) ;
				int rank = rs.getInt(2) ;

				// only use record if its more than a minute ago
				if (now.getTime() - date.getTime() > (60 * 1000)) {

					// use old record if we have one and rank is different from this one
					if (oldDate != null && oldRank != rank)
						break ;
					else {
						oldRank = rank ;
						oldDate = date ;
					}
				}
			}

			rs.close() ;
			stmt.close() ;

			if (oldDate == null)
				return null ;
			else 
				return new RankRecord(oldRank, oldDate) ;
		}


		public Element getDescription() throws SQLException{

			RankRecord lastRank = null ;

			try {
				lastRank = getLastRank() ;
			} catch(ParseException e) {System.out.println("Problem parsing date") ;};



			int currentRank = getRanking() ;

			Element player = parent.doc.createElement("player") ;
			player.setAttribute("name", name) ;
			player.setAttribute("tasksCompleted", String.valueOf(getTasksCompleted())) ;
			player.setAttribute("totalTasks", String.valueOf(getTotalTasks())) ;
			player.setAttribute("averageTaskScore", String.valueOf(getAverageTaskScore())) ;
			player.setAttribute("totalScore", String.valueOf(getTotalScore())) ;

			player.setAttribute("totalPlayers", String.valueOf(getTotalPlayers())) ;

			if (lastRank != null) {
				player.setAttribute("ranking", String.valueOf(getRankAsString(currentRank))) ;

				player.setAttribute("rankChange", String.valueOf(lastRank.rank - currentRank)) ;
				player.setAttribute("lastRankCheck", String.valueOf(lastRank.getTimeAgo())) ;
			}

			Statement stmt = createStatement() ;
			stmt.executeUpdate("INSERT INTO rankRecords VALUES(\"" + name + "\", CURRENT_TIMESTAMP, " + currentRank + ")") ;
			stmt.close() ;



			return player ;
		}


		public Element getBestQueries(int taskId) throws SQLException{

			Element bestQueries = parent.doc.createElement("bestQueries") ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT DISTINCT q.query, q.score, q.recall, q.precision FROM queries q WHERE player='" + name + "' AND task=" + taskId + " ORDER BY score DESC LIMIT 5") ;

			while (rs.next()) {
				String queryText = rs.getString(1) ;
				int score = rs.getInt(2) ;

				Element query = parent.doc.createElement("query") ;
				query.setAttribute("text", rs.getString(1)) ;
				query.setAttribute("score", rs.getString(2)) ;
				query.setAttribute("recall", rs.getString(3)) ;
				query.setAttribute("precision", rs.getString(4)) ;

				bestQueries.appendChild(query) ;
			}

			rs.close() ;
			stmt.close() ;

			return bestQueries ;			
		}

		public Element getTaskScore(int taskId) throws SQLException{

			Element taskScore = parent.doc.createElement("taskScore") ;
			KoruTask task = new KoruTask(taskId) ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT DISTINCT q.query, q.score, q.precision, q.recall, q.docs_returned, q.rel_docs_returned, q.rel_docs_total " 
					+ "FROM queries q WHERE player='" + name 
					+ "' AND task=" + taskId + " ORDER BY score DESC LIMIT 1") ;

			if (rs.first()) {
				String queryText = rs.getString(1) ;
				taskScore.setAttribute("queryText", rs.getString(1)) ;
				taskScore.setAttribute("score", rs.getString(2)) ;
				taskScore.setAttribute("recall", rs.getString(3)) ;
				taskScore.setAttribute("precision", rs.getString(4)) ;
				taskScore.setAttribute("docs_returned", rs.getString(5)) ;
				taskScore.setAttribute("rel_docs_returned", rs.getString(6)) ;
				taskScore.setAttribute("rel_docs_total", rs.getString(7)) ;
				taskScore.setAttribute("rank", getRankAsString(task.getRanking(name))) ;
			}

			rs.close() ;
			stmt.close() ;

			return taskScore ;
		}

		public Element getRecentQueries(int taskId) throws SQLException{

			Element lastQueries = parent.doc.createElement("recentQueries") ;

			Statement stmt = createStatement() ;
			ResultSet rs = stmt.executeQuery("SELECT DISTINCT query, score FROM queries Where player='" + name + "' AND task=" + taskId + " ORDER BY date_time DESC LIMIT 5") ;

			while (rs.next()) {
				String queryText = rs.getString(1) ;
				int score = rs.getInt(2) ;

				Element query = parent.doc.createElement("query") ;
				query.setAttribute("text", queryText) ;
				query.setAttribute("score", String.valueOf(score)) ;

				lastQueries.appendChild(query) ;
			}

			rs.close() ;
			stmt.close() ;

			return lastQueries ;
		}
	}


	public String getRankAsString(int rank) {
		if (rank == 0)
			return "unplaced" ;

		int lastDigit = rank%10 ;

		if (lastDigit == 1)
			return rank + "st" ;

		if (lastDigit == 2)
			return rank + "nd" ;

		if (lastDigit == 3)
			return rank + "rd" ;

		return rank + "th" ;
	}
}
